# -*- coding: utf-8 -*-
import pymysql
from dbutils.pooled_db import PooledDB
from config import DevelopmentConfig


class DataBase(object):

    def __init__(self, host=DevelopmentConfig.HOSTNAME, user=DevelopmentConfig.USERNAME, db=DevelopmentConfig.DATABASE,
                 passwd=DevelopmentConfig.PASSWORD, port=DevelopmentConfig.PORT, mincached=1):
        self._pool = PooledDB(pymysql, maxcached=mincached, host=host, user=user, db=db, passwd=passwd, port=port,
                              charset='utf8mb4', read_timeout=600)

    def getConn(self):
        conn = self._pool.connection()
        cursor = conn.cursor()
        return conn, cursor

    @staticmethod
    def close(cursor, conn):
        """释放连接归还给连接池"""
        cursor.close()
        conn.close()

    def select(self, sql):
        conn, cursor = self.getConn()
        values = ()
        try:
            cursor.execute(sql)
            values = cursor.fetchall()
        except Exception as e:
            print("select:", e, sql)
        finally:
            self.close(cursor, conn)
        return values

    def execute_query_with_collection(self, sql, collection):
        # sql = "SELECT * FROM your_table_name WHERE product_id IN (%s)"
        # 从连接池获取连接,创建游标对象
        conn, cursor = self.getConn()
        try:
            # 使用参数化查询构建 SQL 语句
            # 使用逗号分隔的占位符，根据集合的长度生成相应数量的 %s
            placeholders = ', '.join(['%s'] * len(collection))
            sql = sql % placeholders
            # 执行 SQL 查询
            cursor.execute(sql, tuple(collection))
            # 获取查询结果
            results = cursor.fetchall()
            return results
        except Exception as e:
            print(f"Error: {e}")
        finally:
            # 关闭游标和连接
            self.close(cursor, conn)

    def sql_submit(self, sql, data=None):
        conn, cursor = self.getConn()
        try:
            cursor.execute(sql, data)
            conn.commit()
            return True
        except Exception as e:
            conn.rollback()
            print(e)
            return False
        finally:
            self.close(cursor, conn)

    def delete(self, sql):
        return self.sql_submit(sql)

    def insert(self, sql):
        return self.sql_submit(sql)

    def update(self, sql):
        return self.sql_submit(sql)

    def insert_many(self, sql, data):
        # sql = 'insert into pmid_abstract(pmid,abstract) values (%s,%s)'
        conn, cursor = self.getConn()
        try:
            cursor.executemany(sql, data)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()
        finally:
            self.close(cursor, conn)
